﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using GJDW.SRC.SQL;
using System.Data.SqlClient;
using System.Data.Sql;
using System.Collections;

namespace StateGrid.MDBservice
{
    public class XW_CATALOGService
    {

        /// <summary>
        /// 无参构造函数
        /// </summary>
        public XW_CATALOGService()
        {
        }

        /// <summary>
        /// 读取学位分类表中的分类信息
        /// </summary>
        /// <returns></returns>
        public List<XW_CATALOG> getXW_CATALOGListFromAccess()
        {
            List<XW_CATALOG> result = new List<XW_CATALOG>();
            //连接字符串
            string strConnection = @"Provider=Microsoft.Jet.OleDb.4.0;Data Source=H://20110704调研库//中国成果-主库.mdb";
            //建立连接
            OleDbConnection objConnection = new OleDbConnection(strConnection);

            //打开连接
            objConnection.Open();

            //sql语句
            OleDbCommand sqlcmd = new OleDbCommand(@"select * from XW", objConnection);

            //执行查询
            OleDbDataReader reader = sqlcmd.ExecuteReader();

            while (reader.Read())
            { //这个read调用很重要！不写的话运行时将提示找不到数据
                XW_CATALOG temp = new XW_CATALOG();
                temp.Catalog_code = reader["分类编码"].ToString();
                temp.Catalog_tx = reader["分类体系"].ToString();
                temp.Catalog_note = reader["备注"].ToString();
                temp.Catalog_content = reader["内容"].ToString();
                result.Add(temp);
            }
            objConnection.Close();
            return result;
        }

       /// <summary>
       /// 将中国成果分类的存储到SQL Server数据库
       /// </summary>
       /// <returns></returns>
        public Boolean saveZGCG_CATALOG()
        {
            //从Access中读取中国成功分类表
            List<XW_CATALOG> sourceList = this.getXW_CATALOGListFromAccess();
            //建立连接
            SqlConnection conn = Connection.CONN;
            Connection.openConnection();
            //命令行
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            if (null != sourceList)
            {
                foreach (XW_CATALOG temp in sourceList)
                {
                    cmd.CommandText = "insert into XW_CATALOG(catalog_code,catalog_tx,catalog_note,catalog_content) values('" + temp.Catalog_code + "','" + temp.Catalog_tx + "','" + temp.Catalog_note + "','" + temp.Catalog_content + "')";
                    cmd.ExecuteNonQuery();
                }
            }
            return true;
        }

        /// <summary>
        /// 读取学位分类表中的分类信息
        /// </summary>
        /// <returns>返回从SQL Server数据库中读取的学位论文分类数据</returns>
        public List<XW_CATALOG> getXW_CATALOGList()
        {
            List<XW_CATALOG> result = new List<XW_CATALOG>();
            //建立连接
            SqlConnection conn = Connection.CONN;
            Connection.openConnection();
            //命令行
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = "select * from XW_CATALOG";
            SqlDataReader sda= cmd.ExecuteReader();
            while (sda.Read())
            {
                XW_CATALOG temp = new XW_CATALOG();
                temp.Catalog_code = sda["catalog_code"].ToString();
                temp.Catalog_content = sda["catalog_content"].ToString();
                temp.Catalog_note = sda["catalog_note"].ToString();
                temp.Catalog_tx = sda["catalog_tx"].ToString();
                result.Add(temp);
            }
            sda.Close();
            return result;
        }


        /// <summary>
        /// 读取学位分类表中的分类信息
        /// </summary>
        /// <returns>返回从SQL Server数据库中读取的学位论文分类数据,存放在Hashtable中</returns>
        public Hashtable getXW_CATALOGHash()
        {
            //HashSet<XW_CATALOG> hash = new HashSet<XW_CATALOG>();
            Hashtable result = new Hashtable();

            //建立连接
            SqlConnection conn = Connection.CONN;
            Connection.openConnection();
            //命令行
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = "select * from XW_CATALOG";
            SqlDataReader sda = cmd.ExecuteReader();
            while (sda.Read())
            {
                XW_CATALOG temp = new XW_CATALOG();
                temp.Catalog_code = sda["catalog_code"].ToString();
                temp.Catalog_content = sda["catalog_content"].ToString();
                temp.Catalog_note = sda["catalog_note"].ToString();
                temp.Catalog_tx = sda["catalog_tx"].ToString();
                result.Add(temp.Catalog_code,temp);
            }
            sda.Close();
            return result;
        }
    }
}
